package com.dt.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

/**
 * 在Excel指定位置插入图片
 * @author 啊涛
 * @version 1.0
 * @date 2021/9/10 9:25
 */
public class ExcelInsertPicture {
	public static void main( String[] args ) {
		xlsxExcel();
		xlsExcel();
	}
	public static void xlsxExcel() {
		String excelFile = "C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\周家变电站.xlsx";
		FileOutputStream fileOut = null;
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream( );
		ByteArrayOutputStream byteArrayOutputStream1 = new ByteArrayOutputStream( );
		try {
			BufferedImage bufferedImage = ImageIO.read( new File( "C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png" ) );
			ImageIO.write( bufferedImage , "png" , byteArrayOutputStream );
			BufferedImage bufferedImage1 = ImageIO.read( new File( "C:\\Users\\ThinkPad\\Desktop\\gongzhang.png" ) );
			ImageIO.write( bufferedImage1 , "png" , byteArrayOutputStream1 );
			FileInputStream fileInputStream = new FileInputStream( excelFile );
			XSSFWorkbook workbook=new XSSFWorkbook(fileInputStream);

			PicturePosition picturePosition = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 29 , 33 , ( short ) 37, 38 );
			AddPictureToExcel( workbook , byteArrayOutputStream1 , picturePosition );


			PicturePosition picturePosition1 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 5 , 38 , ( short ) 9, 39 );
			AddPictureToExcel( workbook , byteArrayOutputStream , picturePosition1 );

			PicturePosition picturePosition2 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 13 , 38 , ( short ) 17, 39 );
			AddPictureToExcel( workbook , byteArrayOutputStream , picturePosition2 );

			PicturePosition picturePosition3 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 21, 38 , ( short ) 25, 39 );
			AddPictureToExcel( workbook , byteArrayOutputStream , picturePosition3 );


			fileOut = new FileOutputStream( excelFile );
			workbook.write( fileOut );
			System.out.print( excelFile + "  Success!" );
		} catch ( Exception e ) {
			throw new RuntimeException( e.getMessage( ) );
		} finally {
			try {
				fileOut.close( );
			} catch ( IOException e ) {
				e.printStackTrace( );
			}
		}

	}


	public static void xlsExcel() {
		String excelFile = "C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\周家变电站-10kV周水线306线路保护-WXH822C-201901.xls";
		FileOutputStream fileOut = null;
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream( );
		ByteArrayOutputStream byteArrayOutputStream1 = new ByteArrayOutputStream( );
		try {
			BufferedImage bufferedImage = ImageIO.read( new File( "C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png" ) );
			ImageIO.write( bufferedImage , "png" , byteArrayOutputStream );
			BufferedImage bufferedImage1 = ImageIO.read( new File( "C:\\Users\\ThinkPad\\Desktop\\gongzhang.png" ) );
			ImageIO.write( bufferedImage1 , "png" , byteArrayOutputStream1 );
			FileInputStream fileInputStream = new FileInputStream( excelFile );
			//XSSFWorkbook workbook=new XSSFWorkbook(fileInputStream);
			HSSFWorkbook workbook = new HSSFWorkbook( fileInputStream );

			PicturePosition picturePosition = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 29 , 32 , ( short ) 37, 37 );
			//AddPictureToExcel(workbook, byteArrayOutputStream, picturePosition);
			AddPictureToExcel03( workbook , byteArrayOutputStream1 , picturePosition );


			PicturePosition picturePosition1 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 5 , 37 , ( short ) 9, 38 );
			//AddPictureToExcel(workbook, byteArrayOutputStream, picturePosition);
			AddPictureToExcel03( workbook , byteArrayOutputStream , picturePosition1 );

			PicturePosition picturePosition2 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 13 , 37 , ( short ) 17, 38 );
			//AddPictureToExcel(workbook, byteArrayOutputStream, picturePosition);
			AddPictureToExcel03( workbook , byteArrayOutputStream , picturePosition2 );

			PicturePosition picturePosition3 = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 21, 37 , ( short ) 25, 38 );
			//AddPictureToExcel(workbook, byteArrayOutputStream, picturePosition);
			AddPictureToExcel03( workbook , byteArrayOutputStream , picturePosition3 );


			fileOut = new FileOutputStream( excelFile );
			workbook.write( fileOut );
			System.out.print( excelFile + "  Success!" );
		} catch ( Exception e ) {
			throw new RuntimeException( e.getMessage( ) );
		} finally {
			try {
				fileOut.close( );
			} catch ( IOException e ) {
				e.printStackTrace( );
			}
		}

	}

	public static void AddPictureToExcel( XSSFWorkbook workbook , ByteArrayOutputStream byteArrayOutputStream , PicturePosition picturePosition ) {
		int sheetNumber = workbook.getNumberOfSheets( );
		for ( int i = 0 ; i < sheetNumber ; i++ ) {
			XSSFSheet sheet = workbook.getSheetAt( i );
			XSSFDrawing drawing = sheet.createDrawingPatriarch( );
			XSSFClientAnchor anchor = new XSSFClientAnchor( picturePosition.getDx1( ) , picturePosition.getDx2( ) , picturePosition.getDy1( ) , picturePosition.getDy2( ) , picturePosition.getCol1( ) , picturePosition.getRow1( ) , picturePosition.getCol2( ) , picturePosition.getRow2( ) );
			drawing.createPicture( anchor , workbook.addPicture( byteArrayOutputStream.toByteArray( ) , XSSFWorkbook.PICTURE_TYPE_PNG ) );
		}
	}

	public static void AddPictureToExcel03( HSSFWorkbook workbook , ByteArrayOutputStream byteArrayOutputStream , PicturePosition picturePosition ) {
		int sheetNumber = workbook.getNumberOfSheets( );
		for ( int i = 0 ; i < sheetNumber ; i++ ) {
			HSSFSheet sheet = workbook.getSheetAt( i );
			HSSFPatriarch drawing = sheet.createDrawingPatriarch( );
			HSSFClientAnchor anchor = new HSSFClientAnchor( picturePosition.getDx1( ) , picturePosition.getDx2( ) , picturePosition.getDy1( ) , picturePosition.getDy2( ) , picturePosition.getCol1( ) , picturePosition.getRow1( ) , picturePosition.getCol2( ) , picturePosition.getRow2( ) );
			drawing.createPicture( anchor , workbook.addPicture( byteArrayOutputStream.toByteArray( ) , HSSFWorkbook.PICTURE_TYPE_PNG ) );
		}

	}
}
